# Chapter 20. Python Reference

#### Contents:

Module: MySQL
Module: mSQL

There are actually a handful of Python modules running around that support database access against MySQL and mSQL. They are very similar in most respects. This chapter provides the API specification for two common modules. It is, however, important to note an approaching unification of Python database APIs under a single API being specified by the Python Database SIG. mSQL currently has no support for this API.

## 20.1. Module: MySQL

The entry point into the `MySQL` module is via the `MySQL.connect()` method. The return value from this method represents a connection to a MySQL database that you can use for all of your MySQL operations.

Method: MySQL.connect( ) |

#### Signature

>     connection = MySQL.connect(host)

#### Synopsis

Connects to the MySQL database engine on the specified server. If you call `connect()` with no arguments, it will connect you to the MySQL database engine on the local machine. It returns a Python object representing a connection to a MySQL database.

#### Example

>     conn = MySQL.connect(`carthage.imaginary.com');

Method: connection.selectdb( ) |

#### Signature

>     connection.selectdb(database)

#### Synopsis

Selects the database against which you intend to operate.

#### Example

>     connection.selectdb(`test');

Method: connection.do( ) |

#### Signature

>     results = connection.do(sql)

#### Synopsis

Sends the specified SQL statement to the currently selected database for execution. The results are returned as a list of lists where each list represents a single row. The method is also used for updates -- you just do not process the return value.

#### Example

>     results = conn.do(`SELECT title, year FROM movies');
>     row1 = results[0];

Method: connection.query( ) |

#### Signature

>     statement_handle = connection.query(sql)

#### Synopsis

Like the do() method, this method sends the specified SQL statement to the currently selected database. Unlike the do() method, this method returns a statement handler object that encapsulates data about the results of the SQL query as well as the results themselves.

#### Example

>     hndl = conn.query(`SELECT title, year FROM movies');

Method: statement_handle.affectedrows( ) |

#### Signature

>     rowcount = statement_handle.affectedrows()

#### Synopsis

Assuming the results of the SQL represented by this statement handler came from an `UPDATE`, `DELETE`, or `INSERT`, this method returns the number of rows actually modified by that statement.

#### Example

>     rowcount = hndl.affectedrows()

Method: statement_handle.numrows( ) |

#### Signature

>     rowcount = statement_handle.numrows()

#### Synopsis

Assuming the results of the SQL represented by this statement handler came from a `SELECT`, this method provides the number of rows in the result set.

#### Example

>     rowcount = hndl.numrows()

Method: statement_handle.fields( ) |

#### Signature

>     list = statement_handle.fields()

#### Synopsis

Provides meta-information about the columns in the results returned by this query. The list is actually a list of lists. Each member of the returned list is a list of meta-information about a specific column. In other words, the returned list will have one member for each column in the result set. The first member of the list represents the first column, the second member the second column, and so on.

The meta-data for each column is a list of five elements:

- A string containing the column name

- A string containing the name of the table from which the column came

- A string with the name of the SQL datatype for the column

- An `int` containing the size of the column

- A string containing the column modifies such as `NOTNULL`

#### Example

>     flds = hndl.fields();
>     for column in flds:
>     name = column[0];
>     table = column[1];
>     type = column[2];
>     size = columns[3];
>     mods = column[4];

Method: statement_handle.fetchrows( ) |

#### Signature

>     list = statement_handle.fetchrows(rownum)

#### Synopsis

Fetches the row values of the specified row number associated with the result set represented by the statement handler. If you pass -1 as an argument, this method will return a list of all the rows. For each row in the list, a row is represented by a list whose number of elements equals the number of columns in the result set. The first element represents the first column value, the second element the second column, and so on.

#### Example

>     rows = hndl.fetchrows(-1);
>     for row in rows:
>         col1 = row[0];
>         col2 = row[1];

Method: connection.listdbs( ) |

#### Signature

>     dbs = connection.listdbs()

#### Synopsis

Provides a Python list of databases available on the database server.

#### Example

>     dbs = conn.listdbs()

Method: connection.listtables( ) |

#### Signature

>     tables = connection.listtables();

#### Synopsis

Provides a Python list of tables stored in the selected database.

#### Example

>     tables = conn.listtables();

Copyright © 2001 O'Reilly & Associates. All rights reserved.